package com.foosun.util;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public enum SqlUtil {
	
	INSTANCE;

	public String buildPageSql(String sql, int page, int pageSize) {
		int start = (page - 1) * pageSize;
		StringBuffer local_sql = new StringBuffer();
		local_sql.append(sql);
		local_sql.append(" limit ");
		local_sql.append(start);
		local_sql.append(",");
		local_sql.append(pageSize);
		return local_sql.toString();
	}
	
	public String buildPageSql2(String sql, int page, int pageSize) {
		StringBuffer local_sql = new StringBuffer();
		String order = SqlPaser.findOrderStr(sql);
		if (order != null)
			sql = sql.replaceAll(order, "");
		else
			order = "order by id desc";
		local_sql.append("select * from (");
		local_sql.append(SqlPaser.insertSelectField("ROW_NUMBER() Over("
				+ order + ") as rowNum", sql));
		local_sql.append(") tb where rowNum between ");
		local_sql.append((page - 1) * pageSize + 1);
		local_sql.append(" AND ");
		local_sql.append(page * pageSize);

		return local_sql.toString();
	}
	
	public String buildCountSql(String sql) {
		String ret_sql = removeSelect(sql);
		int idx = ret_sql.lastIndexOf("order by");
		if(idx != -1) {
			ret_sql = ret_sql.substring(0, idx);
		}
		return "SELECT COUNT(*) " + ret_sql;
	}
	
	private String removeSelect(String sql){
		sql=sql.toLowerCase();
		Pattern p = Pattern.compile("\\(.*\\)",Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(sql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			int c = m.end()-m.start();
			m.appendReplacement(sb, getStr(c,"~"));
		}
		m.appendTail(sb);
		String replacedSql = sb.toString();
		return sql.substring(replacedSql.indexOf("from"));
	}
	
	private String getStr(int num, String str) {
		StringBuffer sb = new StringBuffer("");
		for (int i = 0; i < num; i++) {
			sb.append(str);
		}
		return sb.toString();
	}

}
